package com.vadmin.vadminjava.db;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import com.mchange.v2.c3p0.impl.NewProxyPreparedStatement;
import com.vadmin.vadminjava.common.CommonUtils;


import java.io.Serializable;
import java.sql.*;
import java.util.logging.Level;
import java.util.logging.Logger;


/**
 * C3P0移植原com.thinkersoft.jdbc.mysql.DBconn
 * 修改了DBconn内获取Connection的方法。C3P0配置文件自导模式读取
 *
 * @author HMH
 */
public class C3P0 {

    private static final long serialVersionUID = 1L;

    private static final Logger log = Logger.getLogger(C3P0.class.getName());

    //自导模式寻找根目录下的配置文件
    private static ComboPooledDataSource _ds = new ComboPooledDataSource();

    public Connection conn = null;

    ResultSet rs = null;

    int rsi = 0;

    public C3P0() {
        init();
    }

    private void init() {
        try {
            conn = _ds.getConnection();
        } catch (Exception e) {
            Logger.getLogger(this.getClass().getName()).log(Level.SEVERE,CommonUtils.getSystemCurrentTime() + " C3P0 conn打开失败",e);
        }

    }

    public ResultSet executeQuery(String sql) throws Exception {
        String errMsg = "";
        try {
            if (sql.indexOf("load_file(") != -1
                    || sql.indexOf("union") != -1
                    || sql.indexOf("database()") != -1 //|| sql.indexOf("1=1") != -1
            ) {//安全起见，未找到解决原因之前不放开这两个功能
                errMsg = " 非法SQL ";
                throw new Exception();
            }
            Statement stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);
        } catch (Exception ex) {
            Logger.getLogger(this.getClass().getName()).log(Level.SEVERE, CommonUtils.getSystemCurrentTime(), ex);
            throw ex;
        }
        return rs;
    }

    public void executeUpdate(String sql) throws Exception {
        try {
            Statement stmt = conn.createStatement();
            rsi = stmt.executeUpdate(sql);
        } catch (Exception ex) {
            Logger.getLogger(this.getClass().getName()).log(Level.SEVERE, CommonUtils.getSystemCurrentTime(), ex);
            rsi = 0;
            throw ex;
        }
    }

    public void executeUpdateMakeNoMistake(String sql) throws Exception {
        try {
            Statement stmt = conn.createStatement();
            rsi = stmt.executeUpdate(sql);
        } catch (Exception ex) {
            throw ex;
        }
    }

    public int executeUpdateTOnum(String sql) throws Exception {
        int num = 0;
        try {
            Statement stmt = conn.createStatement();
            num = stmt.executeUpdate(sql);
        } catch (Exception ex) {
            Logger.getLogger(this.getClass().getName()).log(Level.SEVERE, CommonUtils.getSystemCurrentTime(), ex);//
            throw ex;
        }
        return num;
    }

    public void executeUpdateArray(String sql[]) {
        try {
            Statement stmt = conn.createStatement();
            for (int i = 0; i < sql.length; i++) {
                stmt.executeUpdate(sql[i]);
            }
        } catch (Exception ex) {
            Logger.getLogger(this.getClass().getName()).log(Level.SEVERE, CommonUtils.getSystemCurrentTime(), ex);
        }
    }

    public int executeUpdateR(String sql) {//这方法有问题，不能用，是想回调自动增量的值
        int i = -1;
        try {
            Statement stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
                    ResultSet.CONCUR_UPDATABLE);
            stmt.executeUpdate(sql);
            ResultSet rs = stmt.getResultSet();
            ResultSetMetaData md = rs.getMetaData();
            int length = md.getColumnCount();
            if (rs.next()) {
                i = rs.getInt(1);
            }
        } catch (Exception ex) {
            Logger.getLogger(this.getClass().getName()).log(Level.SEVERE, CommonUtils.getSystemCurrentTime(), ex);
        }
        return i;
    }

    public String executeUpdateGetAutoIncrement(String sql) {//返回自动增量的值
        Serializable ret = null;
        try {
            NewProxyPreparedStatement state = (NewProxyPreparedStatement) conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            ResultSet rs = null;
            state.executeUpdate();
            rs = state.getGeneratedKeys();
            if (rs.next()) {
                ret = (Serializable) rs.getObject(1);
            }
        } catch (Exception ex) {
            Logger.getLogger(this.getClass().getName()).log(Level.SEVERE, CommonUtils.getSystemCurrentTime(), ex);
        }
        return ret.toString();
    }

    //获得自动增量的最新记录
    public String getAutoIncrement(String table, String field) {
        String sql = "select " + field + " from " + table + " ORDER BY " + field + " desc limit 1";
        try {
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(sql);
            if (rs.next()) {
                field = rs.getString(field);
            }
        } catch (Exception ex) {
            Logger.getLogger(this.getClass().getName()).log(Level.SEVERE, CommonUtils.getSystemCurrentTime(), ex);
        }
        return field;
    }

    /**
     * 自增主键主键插入值后获取自增ID
     * <p>
     * 时间:2014-09-25
     * <p>
     * 作者:HMH
     *
     * @param sql
     * @return
     */
    public int insertIntoDB(String sql) {
        Statement state = null;
        ResultSet rs = null;
        int key = -1;
        try {
            state = conn.createStatement();
            state.executeUpdate(sql, Statement.RETURN_GENERATED_KEYS);
            rs = state.getGeneratedKeys();
            if (rs.next()) {
                key = rs.getInt(1);
            }
            return key;
        } catch (SQLException e) {
            Logger.getLogger(this.getClass().getName()).log(Level.SEVERE, CommonUtils.getSystemCurrentTime(), e);
            return key;
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (Exception ex) {
                }
            }
        }
    }

    public String[][] getTable() throws Exception {
        if (rs == null) {
            throw new Exception(new java.util.Date() + "getTable Error:请先执行SQL操作语句");
        }
        return null;
    }

    public Connection getConnection() {
        return conn;
    }

    public void close() throws SQLException {

        if (rs != null) {
            rs.close();
        }
        if (conn != null) {

            conn.close();
        }
    }


    public void closeNoErr() {
        try {
            if (rs != null) {
                rs.close();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        try {
            if (conn != null) {

                conn.close();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public void close(String info) throws SQLException {
        if (rs != null) {
            rs.close();
        }
        if (conn != null) {
            conn.close();
        }
    }

    public int getRSlength() {
        return rsi;
    }

}
